use serde::{Deserialize, Serialize};
use tauri::{Manager, State};
use tauri_plugin_sql::{Migration, MigrationKind};
use chrono::Utc;
use serde_json::Value;
use sqlx::{Pool, Sqlite, SqlitePool, Row};
use std::sync::Arc;
use tokio::sync::Mutex;

#[derive(Debug, Serialize, Deserialize, Clone)]
struct TodoItem {
    id: i64,
    title: String,
    content: Option<String>,
    completed: bool,
    created_at: String,
    updated_at: String,
}

#[derive(Debug, Serialize, Deserialize, Clone)]
struct Note {
    id: i64,
    title: String,
    content: String,
    todo_id: Option<i64>,
    created_at: String,
    updated_at: String,
}

#[derive(Debug, Deserialize)]
struct CreateTodoRequest {
    title: String,
    content: Option<String>,
}

#[derive(Debug, Deserialize)]
struct UpdateTodoRequest {
    id: i64,
    title: Option<String>,
    content: Option<String>,
    completed: Option<bool>,
}

#[derive(Debug, Deserialize)]
struct CreateNoteRequest {
    title: String,
    content: String,
    todo_id: Option<i64>,
}

#[derive(Debug, Deserialize)]
struct UpdateNoteRequest {
    id: i64,
    title: Option<String>,
    content: Option<String>,
    todo_id: Option<i64>,
}

// 数据库状态类型
type DbPool = Arc<Mutex<Pool<Sqlite>>>;

// 数据库辅助函数
async fn execute_sql(pool: &Pool<Sqlite>, sql: &str, params: Vec<Value>) -> Result<Value, String> {
    let mut query = sqlx::query(sql);
    
    for param in params {
        match param {
            Value::String(s) => query = query.bind(s),
            Value::Number(n) => {
                if let Some(i) = n.as_i64() {
                    query = query.bind(i);
                } else if let Some(f) = n.as_f64() {
                    query = query.bind(f);
                }
            },
            Value::Bool(b) => query = query.bind(b),
            Value::Null => query = query.bind(Option::<String>::None),
            _ => return Err("Unsupported parameter type".to_string()),
        }
    }
    
    let result = query.execute(pool)
        .await
        .map_err(|e| format!("SQL execution failed: {}", e))?;
    
    let mut response = serde_json::Map::new();
    response.insert("lastInsertId".to_string(), Value::Number(result.last_insert_rowid().into()));
    response.insert("changes".to_string(), Value::Number(result.rows_affected().into()));
    
    Ok(Value::Object(response))
}

async fn select_sql(pool: &Pool<Sqlite>, sql: &str, params: Vec<Value>) -> Result<Vec<Value>, String> {
    let mut query = sqlx::query(sql);
    
    for param in params {
        match param {
            Value::String(s) => query = query.bind(s),
            Value::Number(n) => {
                if let Some(i) = n.as_i64() {
                    query = query.bind(i);
                } else if let Some(f) = n.as_f64() {
                    query = query.bind(f);
                }
            },
            Value::Bool(b) => query = query.bind(b),
            Value::Null => query = query.bind(Option::<String>::None),
            _ => return Err("Unsupported parameter type".to_string()),
        }
    }
    
    let rows = query.fetch_all(pool)
        .await
        .map_err(|e| format!("SQL select failed: {}", e))?;
    
    let mut results = Vec::new();
    
    // 对于todos表和notes表，我们手动构建JSON对象
    for row in rows {
        let mut obj = serde_json::Map::new();
        
        // 试图获取常见字段
        if let Ok(id) = row.try_get::<i64, _>("id") {
            obj.insert("id".to_string(), Value::Number(id.into()));
        }
        if let Ok(title) = row.try_get::<String, _>("title") {
            obj.insert("title".to_string(), Value::String(title));
        }
        if let Ok(content) = row.try_get::<Option<String>, _>("content") {
            obj.insert("content".to_string(), match content {
                Some(c) => Value::String(c),
                None => Value::Null,
            });
        }
        if let Ok(completed) = row.try_get::<Option<bool>, _>("completed") {
            obj.insert("completed".to_string(), match completed {
                Some(c) => Value::Bool(c),
                None => Value::Bool(false),
            });
        }
        if let Ok(todo_id) = row.try_get::<Option<i64>, _>("todo_id") {
            obj.insert("todo_id".to_string(), match todo_id {
                Some(id) => Value::Number(id.into()),
                None => Value::Null,
            });
        }
        if let Ok(created_at) = row.try_get::<String, _>("created_at") {
            obj.insert("created_at".to_string(), Value::String(created_at));
        }
        if let Ok(updated_at) = row.try_get::<String, _>("updated_at") {
            obj.insert("updated_at".to_string(), Value::String(updated_at));
        }
        
        results.push(Value::Object(obj));
    }
    
    Ok(results)
}

// Learn more about Tauri commands at https://tauri.app/develop/calling-rust/
#[tauri::command]
fn greet(name: &str) -> String {
    format!("Hello, {}! You've been greeted from Rust!", name)
}

#[cfg_attr(mobile, tauri::mobile_entry_point)]
pub fn run() {
    tauri::Builder::default()
        .plugin(
            tauri_plugin_sql::Builder::default()
                .add_migrations("sqlite:todos.db", vec![
                    Migration {
                        version: 1,
                        description: "create_todos_table",
                        sql: "
                            CREATE TABLE IF NOT EXISTS todos (
                                id INTEGER PRIMARY KEY AUTOINCREMENT,
                                title TEXT NOT NULL,
                                content TEXT,
                                completed BOOLEAN NOT NULL DEFAULT 0,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
                            );
                        ",
                        kind: MigrationKind::Up,
                    },
                    Migration {
                        version: 2,
                        description: "create_notes_table",
                        sql: "
                            CREATE TABLE IF NOT EXISTS notes (
                                id INTEGER PRIMARY KEY AUTOINCREMENT,
                                title TEXT NOT NULL,
                                content TEXT NOT NULL,
                                todo_id INTEGER,
                                created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                                updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
                                FOREIGN KEY (todo_id) REFERENCES todos (id) ON DELETE SET NULL
                            );
                        ",
                        kind: MigrationKind::Up,
                    },
                ])
                .build()
        )
        .plugin(tauri_plugin_opener::init())
        .setup(|app| {
            tauri::async_runtime::block_on(async move {
                // 获取应用数据目录路径
                let app_data_dir = app.path().app_data_dir().unwrap();
                let _ = std::fs::create_dir_all(&app_data_dir);
                let db_path = app_data_dir.join("todos.db");
                
                // 创建数据库连接池
                let options = sqlx::sqlite::SqliteConnectOptions::new()
                    .filename(db_path)
                    .create_if_missing(true);
                    
                match SqlitePool::connect_with(options).await {
                    Ok(pool) => {
                        app.manage(Arc::new(Mutex::new(pool)));
                    },
                    Err(e) => {
                        eprintln!("Failed to create database pool: {}", e);
                    }
                }
            });
            Ok(())
        })
        .invoke_handler(tauri::generate_handler![
            greet,
            init_database,
            get_todos,
            create_todo,
            update_todo,
            delete_todo,
            get_notes,
            create_note,
            update_note,
            delete_note
        ])
        .run(tauri::generate_context!())
        .expect("error while running tauri application");
}

// 初始化数据库
#[tauri::command]
async fn init_database(db_pool: State<'_, DbPool>) -> Result<String, String> {
    let pool = db_pool.lock().await;
    let _result = execute_sql(&*pool, "SELECT 1", vec![]).await?;
    
    Ok("Database initialized successfully with SQLite storage".to_string())
}

// 获取所有待办事项
#[tauri::command]
async fn get_todos(db_pool: State<'_, DbPool>) -> Result<Vec<TodoItem>, String> {
    let pool = db_pool.lock().await;
    let rows = select_sql(&*pool, 
        "SELECT id, title, content, completed, created_at, updated_at FROM todos ORDER BY created_at DESC", 
        vec![]
    ).await?;
    
    let mut todos = Vec::new();
    for row in rows {
        if let Ok(todo) = serde_json::from_value::<TodoItem>(row) {
            todos.push(todo);
        }
    }
    
    Ok(todos)
}

// 创建新的待办事项
#[tauri::command]
async fn create_todo(db_pool: State<'_, DbPool>, request: CreateTodoRequest) -> Result<TodoItem, String> {
    let pool = db_pool.lock().await;
    let now = Utc::now().format("%Y-%m-%d %H:%M:%S").to_string();
    
    let content_str = request.content.as_deref().unwrap_or("");
    
    let result = execute_sql(&*pool,
        "INSERT INTO todos (title, content, completed, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
        vec![
            Value::String(request.title.clone()),
            Value::String(content_str.to_string()),
            Value::Bool(false),
            Value::String(now.clone()),
            Value::String(now.clone()),
        ]
    ).await?;
    
    let id = if let Some(last_insert_id) = result.get("lastInsertId") {
        last_insert_id.as_i64().unwrap_or(0)
    } else {
        0
    };
    
    // 返回创建的待办事项
    Ok(TodoItem {
        id,
        title: request.title,
        content: request.content,
        completed: false,
        created_at: now.clone(),
        updated_at: now,
    })
}

// 更新待办事项
#[tauri::command]
async fn update_todo(db_pool: State<'_, DbPool>, request: UpdateTodoRequest) -> Result<TodoItem, String> {
    let pool = db_pool.lock().await;
    let now = Utc::now().format("%Y-%m-%d %H:%M:%S").to_string();
    
    // 首先检查待办事项是否存在
    let existing_rows = select_sql(&*pool,
        "SELECT id, title, content, completed, created_at, updated_at FROM todos WHERE id = ?",
        vec![Value::Number(request.id.into())]
    ).await?;
    
    if existing_rows.is_empty() {
        return Err("Todo not found".to_string());
    }
    
    let mut todo: TodoItem = serde_json::from_value(existing_rows[0].clone())
        .map_err(|e| format!("Failed to parse todo: {}", e))?;
    
    // 更新字段
    if let Some(title) = request.title {
        todo.title = title;
    }
    if let Some(content) = request.content {
        todo.content = Some(content);
    }
    if let Some(completed) = request.completed {
        todo.completed = completed;
    }
    todo.updated_at = now.clone();
    
    // 更新数据库
    let content_str = todo.content.as_deref().unwrap_or("");
    execute_sql(&*pool,
        "UPDATE todos SET title = ?, content = ?, completed = ?, updated_at = ? WHERE id = ?",
        vec![
            Value::String(todo.title.clone()),
            Value::String(content_str.to_string()),
            Value::Bool(todo.completed),
            Value::String(todo.updated_at.clone()),
            Value::Number(request.id.into()),
        ]
    ).await?;
    
    Ok(todo)
}

// 删除待办事项
#[tauri::command]
async fn delete_todo(db_pool: State<'_, DbPool>, id: i64) -> Result<(), String> {
    let pool = db_pool.lock().await;
    let result = execute_sql(&*pool,
        "DELETE FROM todos WHERE id = ?",
        vec![Value::Number(id.into())]
    ).await?;
    
    if let Some(changes) = result.get("changes") {
        if changes.as_i64().unwrap_or(0) > 0 {
            Ok(())
        } else {
            Err("Todo not found".to_string())
        }
    } else {
        Err("Delete operation failed".to_string())
    }
}

// 获取所有便签
#[tauri::command]
async fn get_notes(db_pool: State<'_, DbPool>) -> Result<Vec<Note>, String> {
    let pool = db_pool.lock().await;
    let rows = select_sql(&*pool,
        "SELECT id, title, content, todo_id, created_at, updated_at FROM notes ORDER BY created_at DESC",
        vec![]
    ).await?;
    
    let mut notes = Vec::new();
    for row in rows {
        if let Ok(note) = serde_json::from_value::<Note>(row) {
            notes.push(note);
        }
    }
    
    Ok(notes)
}

// 创建新便签
#[tauri::command]
async fn create_note(db_pool: State<'_, DbPool>, request: CreateNoteRequest) -> Result<Note, String> {
    let pool = db_pool.lock().await;
    let now = Utc::now().format("%Y-%m-%d %H:%M:%S").to_string();
    
    let todo_id_value = if let Some(todo_id) = request.todo_id {
        Value::Number(todo_id.into())
    } else {
        Value::Null
    };
    
    let result = execute_sql(&*pool,
        "INSERT INTO notes (title, content, todo_id, created_at, updated_at) VALUES (?, ?, ?, ?, ?)",
        vec![
            Value::String(request.title.clone()),
            Value::String(request.content.clone()),
            todo_id_value,
            Value::String(now.clone()),
            Value::String(now.clone()),
        ]
    ).await?;
    
    let id = if let Some(last_insert_id) = result.get("lastInsertId") {
        last_insert_id.as_i64().unwrap_or(0)
    } else {
        0
    };
    
    // 返回创建的便签
    Ok(Note {
        id,
        title: request.title,
        content: request.content,
        todo_id: request.todo_id,
        created_at: now.clone(),
        updated_at: now,
    })
}

// 更新便签
#[tauri::command]
async fn update_note(db_pool: State<'_, DbPool>, request: UpdateNoteRequest) -> Result<Note, String> {
    let pool = db_pool.lock().await;
    let now = Utc::now().format("%Y-%m-%d %H:%M:%S").to_string();
    
    // 首先检查便签是否存在
    let existing_rows = select_sql(&*pool,
        "SELECT id, title, content, todo_id, created_at, updated_at FROM notes WHERE id = ?",
        vec![Value::Number(request.id.into())]
    ).await?;
    
    if existing_rows.is_empty() {
        return Err("Note not found".to_string());
    }
    
    let mut note: Note = serde_json::from_value(existing_rows[0].clone())
        .map_err(|e| format!("Failed to parse note: {}", e))?;
    
    // 更新字段
    if let Some(title) = request.title {
        note.title = title;
    }
    if let Some(content) = request.content {
        note.content = content;
    }
    if let Some(todo_id) = request.todo_id {
        note.todo_id = Some(todo_id);
    }
    note.updated_at = now.clone();
    
    let todo_id_value = if let Some(todo_id) = note.todo_id {
        Value::Number(todo_id.into())
    } else {
        Value::Null
    };
    
    // 更新数据库
    execute_sql(&*pool,
        "UPDATE notes SET title = ?, content = ?, todo_id = ?, updated_at = ? WHERE id = ?",
        vec![
            Value::String(note.title.clone()),
            Value::String(note.content.clone()),
            todo_id_value,
            Value::String(note.updated_at.clone()),
            Value::Number(request.id.into()),
        ]
    ).await?;
    
    Ok(note)
}

// 删除便签
#[tauri::command]
async fn delete_note(db_pool: State<'_, DbPool>, id: i64) -> Result<(), String> {
    let pool = db_pool.lock().await;
    let result = execute_sql(&*pool,
        "DELETE FROM notes WHERE id = ?",
        vec![Value::Number(id.into())]
    ).await?;
    
    if let Some(changes) = result.get("changes") {
        if changes.as_i64().unwrap_or(0) > 0 {
            Ok(())
        } else {
            Err("Note not found".to_string())
        }
    } else {
        Err("Delete operation failed".to_string())
    }
}
